
-- **************************************************************************
--    Project Name:   经营指标-指标库应用网点维度日表
--    Job Name:       jms_dwm.dm_overall_target_network_sum_day_dt
--    Author :        季修魁
--    date：          2023/06/27
--    modify :        季修魁
--    date：          2023/07/25
--    modify :        季修魁
--    date：          2023/11/23   优化更新指标数量
-- **************************************************************************
-- **************************************************************************


---1.揽收率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
  from (
select dt                      as stat_date
       ,'D'                    as stat_period
       ,sender_network_code    as network_code
       ,sender_network_name    as network_name
       ,'揽收及时率'             as kpi_name
       ,2                      as kpi_type
       ,sum(timely_cnt)        as kpi_fz
       ,sum(all_cnt)           as kpi_fm
       ,sum(timely_cnt)        as kpi_values
       ,100                    as kpi_param
       ,'苏宁'                 as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_taking_timely_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis00000082'     AS kpi_code
  from jms_dm.dm_taking_timely_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <='{{ execution_date | cst_ds }}'
 group by sender_network_code,sender_network_name,dt
 union all
select dt                      as stat_date
       ,'D'                    as stat_period
       ,sender_network_code    as network_code
       ,sender_network_name    as network_name
       ,'七星潭揽收及时率'        as kpi_name
       ,2                      as kpi_type
       ,sum(timely_cnt)        as kpi_fz
       ,sum(all_cnt)           as kpi_fm
       ,sum(timely_cnt)        as kpi_values
       ,100                    as kpi_param
       ,'苏宁'                  as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_taking_timely_summary_dt'  as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'bis0000000564'   as kpi_code
  from jms_dm.dm_taking_timely_summary_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <='{{ execution_date | cst_ds }}'
 and order_source_name = '七星潭'
 group by sender_network_code,sender_network_name,dt
) a
distribute by dt,kpi_code
;

---4.交件准点率2

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                      as stat_date
       ,'D'                    as stat_period
       ,receive_network_code   as network_code
       ,receive_network_name   as network_name
       ,'交件准点率2'           as kpi_name
       ,2                      as kpi_type
       ,sum(hand_over_in_time_num)          as kpi_fz
       ,sum(should_hand_over_num)           as kpi_fm
       ,sum(hand_over_in_time_num)          as kpi_values
       ,100                    as kpi_param
       ,'熊伟'                 as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_waybill_collect_intime_taking_agg'  as remark
       ,'t-2'             as kpi_timely
       ,dt  as dt
       ,'bis00000304'     AS kpi_code
  from jms_dm.dm_waybill_collect_intime_taking_agg
 where dt >=date_sub('{{ execution_date | cst_ds }}',2) and dt <=date_sub('{{ execution_date | cst_ds }}',1)
 group by receive_network_code,receive_network_name,dt
distribute by dt,kpi_code
;

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                   as stat_date
       ,'D'                    as stat_period
       ,receive_network_code   as network_code
       ,receive_network_name   as network_name
       ,'交件准点率1'             as kpi_name
       ,2                      as kpi_type
       ,sum(hand_over_in_time_num)          as kpi_fz
       ,sum(should_hand_over_num)           as kpi_fm
       ,sum(hand_over_in_time_num)          as kpi_values
       ,100                    as kpi_param
       ,'熊伟'                  as kpi_user
       ,'2天'                  as redo_date_cnt
       ,'网点维度指标，取jms_dm.dm_waybill_collect_intime_taking_agg'  as remark
       ,'t-1'             as kpi_timely
       , dt  as dt
       ,'bis00000136'     AS kpi_code
  from jms_dm.dm_waybill_collect_intime_taking_agg
where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt <= '{{ execution_date | cst_ds }}'
group by receive_network_code,receive_network_name,dt
distribute by dt,kpi_code;



---3.桃花岛上门取件低于75分网点数量

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'桃花岛上门取件低于75分网点数量'                   as kpi_name
       ,1                          as kpi_type
       ,kpi_fz                     as kpi_fz
       ,1                          as kpi_fm
       ,kpi_fz                     as kpi_values
       ,1                          as kpi_param
       ,'季修魁'                   as kpi_user
       ,'1天'                      as redo_date_cnt
       ,'代理区维度指标，取jms_dm.dm_tab_door_pick_up_dm'   as remark
       ,'t-1'             as kpi_timely
       ,dt
       ,'ops00000154'              as kpi_code
from (
select b.agent_code             AS network_code
       ,b.agent_name            as network_name
       ,'ops00000154'           as kpi_code
       ,'桃花岛上门取件低于75分网点数量'      as kpi_name
       ,count(1)                as kpi_fz
       ,1                       as kpi_fm
       ,1                       as kpi_type
       ,a.dt                    as dt
from
(
SELECT
    organization_code AS network_code,
    organization_name AS network_name,
    sum(CASE WHEN take_parts_rate >= 99 AND take_parts_rate <= 100 THEN 20
             WHEN take_parts_rate >= 98 AND take_parts_rate < 99 THEN 16
             WHEN take_parts_rate >= 97 AND take_parts_rate < 98 THEN 14
             WHEN take_parts_rate >= 95 AND take_parts_rate < 97 THEN 12
             WHEN take_parts_rate >= 90 AND take_parts_rate < 95 THEN 10
             WHEN take_parts_rate >= 85 AND take_parts_rate < 90 THEN 6
             WHEN take_parts_rate >= 0 AND take_parts_rate < 85 THEN 0
           END) +
    sum(CASE WHEN cancellation_rate >= 0 AND cancellation_rate <= 7 THEN 30
             WHEN cancellation_rate > 7 AND cancellation_rate <= 8 THEN 26
             WHEN cancellation_rate > 8 AND cancellation_rate <= 9 THEN 22
             WHEN cancellation_rate > 9 AND cancellation_rate <= 10 THEN 18
             WHEN cancellation_rate > 10 AND cancellation_rate <= 12 THEN 14
             WHEN cancellation_rate > 12 AND cancellation_rate <= 15 THEN 10
             WHEN cancellation_rate > 15 AND cancellation_rate <= 20 THEN 6
             WHEN cancellation_rate > 20 AND cancellation_rate <= 30 THEN 2
             WHEN cancellation_rate > 30 AND cancellation_rate <= 100 THEN 0
           END) +
    sum(CASE WHEN default_rate = 0 THEN 15
             WHEN default_rate > 0 AND default_rate <= 0.01 THEN 10
             WHEN default_rate > 0.01 AND default_rate <= 0.3 THEN 6
             WHEN default_rate > 0.3 AND default_rate <= 1 THEN 3
             WHEN default_rate > 1 AND default_rate < 100 THEN 0
           END) +
    SUM(CASE WHEN complaint_rate >= 0 AND complaint_rate <= 0.03 THEN 35
             WHEN complaint_rate > 0.03 AND complaint_rate <= 0.04 THEN 28
             WHEN complaint_rate > 0.04 AND complaint_rate <= 0.06 THEN 25
             WHEN complaint_rate > 0.06 AND complaint_rate <= 0.08 THEN 20
             WHEN complaint_rate > 0.08 AND complaint_rate <= 0.10 THEN 14
             WHEN complaint_rate > 0.10 AND complaint_rate <= 0.20 THEN 12
             WHEN complaint_rate > 0.2 AND complaint_rate <= 0.5 THEN 10
             WHEN complaint_rate > 0.5 AND complaint_rate <= 1 THEN 4
             WHEN complaint_rate > 1 AND complaint_rate <= 100 THEN 0
           END) AS sm_score,
    DT
FROM
    (SELECT
        organization_code,
        organization_name,
        sum(total_single_quantity) AS total_single_quantity,
        CASE
            WHEN sum(pick_up_quantity) = 0 THEN 0
            ELSE sum(take_parts_and_measure_in_time) / sum(pick_up_quantity) * 100
        END AS take_parts_rate,
        CASE
            WHEN sum(total_single_quantity) = 0 THEN 0
            ELSE sum(cancellation_amount) / sum(total_single_quantity) * 100
        END AS cancellation_rate,
        CASE
            WHEN sum(amount_of_breach_of_contract) = 0 THEN 0
            ELSE sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100
        END AS default_rate,
        CASE
            WHEN sum(total_single_quantity) = 0 THEN 0
            ELSE sum(complaints) / sum(total_single_quantity) * 100
        END AS complaint_rate,
        '{{ execution_date | cst_ds }}'  AS  DT,
        'D'           AS  stat_period
    FROM
        jms_dm.dm_tab_door_pick_up_dm a
    WHERE dt ='{{ execution_date | cst_ds }}'
    GROUP BY
        organization_code,
        organization_name
        )  t
GROUP BY
    organization_code,
    organization_name,
    DT
    ) a
 join (select code,agent_code,agent_name,virt_code,virt_name
             from jms_dim.dim_network_whole_massage
---             WHERE agent_name in ('甘肃代理区','青海代理区','宁夏代理区')
            group by agent_code,agent_name,virt_code,virt_name,code
      ) b
  on a.network_code= b.code
 where a.sm_score <75
group by b.agent_code
       ,b.agent_name
       ,a.dt
  ) tt distribute by dt,kpi_code;

-- 虚假揽收率
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,network_code               as network_code
       ,network_name               as network_name
       ,'虚假揽收率'               as  kpi_name
       ,2                          as kpi_type
       ,sum(kpi_fz)                as kpi_fz
       ,sum(kpi_fm)                as kpi_fm
       ,null                       as kpi_values
       ,100                        as kpi_param
       ,'熊伟'                     as kpi_user
       ,'2天'                      as redo_date_cnt
       ,'网点维度指标，取分子虚假揽收数jms_dm.dm_waybill_false_taking_dt,分母应交件jms_dm.dm_waybill_collect_intime_taking_agg'   as remark
       ,'t-1'                      as kpi_timely
       ,dt                         as dt
       ,'bis00000390'              as  kpi_code
from (
    select
        network_code
        ,network_name
        ,'bis00000390'             as kpi_code
        ,'虚假揽收率'              as kpi_name
        ,nvl(sum(kpi_fz), 0)       as kpi_fz
        ,nvl(sum(kpi_fm), 0)       as kpi_fm
        ,2                         as kpi_type
        ,dt                        as dt
    from (
        -- 虚假揽收数
        select
            receive_network_code       as network_code
            ,receive_network_name      as network_name
            ,nvl(count(*),0)           as kpi_fz
            ,0                         as kpi_fm
            ,dt
        from
            jms_dm.dm_waybill_false_taking_dt
        where  dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
        group by receive_network_code,receive_network_name, dt
        union all
        -- 应交件票数
        select
        receive_network_code       as network_code
        ,receive_network_name      as network_name
        ,0                                  as kpi_fz
        ,nvl(sum(should_hand_over_num),0)   as kpi_fm
        ,dt
        from
        jms_dm.dm_waybill_collect_intime_taking_agg
        where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
        group by receive_network_code,receive_network_name,dt
    ) tbl
        group by network_code,network_name,dt
) t group by network_code,network_name,dt
distribute by dt,kpi_code;


-- 虚假揽收票数
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select dt                          as stat_date
       ,'D'                        as stat_period
       ,receive_network_code               as network_code
       ,receive_network_name               as network_name
       ,'虚假揽收票数'               as  kpi_name
       ,1                          as kpi_type
       ,nvl(count(*),0)                as kpi_fz
       ,1                          as kpi_fm
       ,nvl(count(*),0)                as kpi_values
       ,1                          as kpi_param
       ,'熊伟'                     as kpi_user
       ,'2天'                      as redo_date_cnt
       ,'网点维度指标，取分子虚假揽收数jms_dm.dm_waybill_false_taking_dt'   as remark
       ,'t-1'             as kpi_timely
       ,dt                         as dt
       ,'bis00000371'              as  kpi_code
  from jms_dm.dm_waybill_false_taking_dt
 where dt >=date_sub('{{ execution_date | cst_ds }}',1) and dt<='{{ execution_date | cst_ds }}'
 group by receive_network_code,receive_network_name, dt
distribute by dt,kpi_code;

---桃花岛指标
insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select *
  from (
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-取件及时率'             as kpi_name
       ,2                                       as kpi_type
       ,take_parts_and_measure_in_time          as kpi_fz
       ,pick_up_quantity                        as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000062'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-取消率'                     as kpi_name
       ,2                                       as kpi_type
       ,cancellation_amount                     as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000061'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-投诉率'                 as kpi_name
       ,2                                       as kpi_type
       ,complaints                              as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000064'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
union all
select dt                                       as stat_date
       ,'D'                                     as stat_period
       ,network_code                            as network_code
       ,network_name                            as network_name
       ,'桃花岛上门取件-爽约率'                 as kpi_name
       ,2                                       as kpi_type
       ,amount_of_breach_of_contract            as kpi_fz
       ,total_single_quantity                   as kpi_fm
       ,null                                    as kpi_values
       ,100                                     as kpi_param
       ,'禹铄'                                  as kpi_user
       ,'1天'                                   as redo_date_cnt
       ,'网点维度指标，取桃花岛平台直接计算后的结果数据,t-1时效指标'   as remark
       ,'t-1'                                   as kpi_timely
       ,dt                                      as dt
       ,'ccm0000000063'                         as kpi_code
  from (
 select organization_code                     as network_code
       ,max(organization_name)                as network_name
       ,sum(take_parts_and_measure_in_time)   as take_parts_and_measure_in_time          --取件及时量
       ,sum(pick_up_quantity)                 as pick_up_quantity                        --取件量
       ,case when sum(pick_up_quantity) = 0 then 0 else sum(take_parts_and_measure_in_time)/sum(pick_up_quantity) * 100  end as take_parts_rate      --取件及时率
       ,sum(cancellation_amount)              as cancellation_amount                     --取消量
       ,sum(total_single_quantity)            as total_single_quantity                   --总单量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(cancellation_amount) / sum(total_single_quantity) * 100 end as cancellation_rate    --取消率
       ,sum(amount_of_breach_of_contract)     as amount_of_breach_of_contract            --爽约量
       ,case when sum(amount_of_breach_of_contract) = 0 then 0 else sum(amount_of_breach_of_contract) / sum(total_single_quantity) * 100 end as default_rate  --爽约率
       ,sum(complaints)                       as    complaints                                       --投诉量
       ,case when sum(total_single_quantity) = 0 then 0 else sum(complaints) / sum(total_single_quantity) * 100 end as complaint_rate    ---投诉率
       ,dt
  from jms_dm.dm_tab_door_pick_up_dm a
 where dt ='{{ execution_date | cst_ds }}'
 group by organization_code,dt ) t1
) tt
distribute by dt,kpi_code;

--网点-整体上传及时率

insert overwrite table jms_dwm.dwm_overall_target_network_sum_mid_dt partition (dt,kpi_code)
select * from
(
 select dt                            as stat_date
        ,'D'                          as stat_period
        ,network_code                 as network_code
        ,network_name                 as network_name
        ,'网点-整体上传及时率'           as kpi_name
        ,2                            as kpi_type
        ,sum(all_cnt)                               as kpi_fz
        ,(sum(all_cnt)+sum(all_no_cnt))             as kpi_fm
        ,null                         as kpi_values
        ,100                          as kpi_param
        ,'郭瑞玲'                      as kpi_user
        ,'1天'                        as redo_date_cnt
        ,'jms_dm.dm_upload_in_time_count_dt'  as remark
        ,'t-1'             as kpi_timely
        ,dt
        ,'bis00000187'     AS kpi_code
  from jms_dm.dm_upload_in_time_count_dt
 where dt between date_add('{{ execution_date | cst_ds }}',-0)  and '{{ execution_date | cst_ds }}'
   and cnt_type=1
 group by network_code,network_name,dt
 ) t1
 distribute by dt,kpi_code
 ;

insert overwrite table jms_dwm.dwm_overall_target_network_sum_day_dt partition (dt)
select a.stat_date
       ,b.network_type
       ,a.network_code
       ,a.network_name
       ,b.fran_code
       ,b.fran_name
       ,b.agent_code
       ,b.agent_name
       ,b.virt_code
       ,b.virt_name
       ,a.kpi_code
       ,a.kpi_name
       ,a.kpi_type
       ,a.kpi_fz
       ,a.kpi_fm
       ,a.kpi_fz
       ,a.kpi_param
       ,c.principal_name
       ,d.principal
       ,b.principal
       ,a.dt
from (
select * from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-1'
union all
select date_add(stat_date,1) as stat_date
       ,stat_period
       ,network_code
       ,network_name
       ,kpi_name
       ,kpi_type
       ,kpi_fz
       ,kpi_fm
       ,kpi_values
       ,kpi_param
       ,kpi_user
       ,redo_date_cnt
       ,remark
       ,kpi_timely
       ,date_add(dt,1)  as dt
       ,kpi_code
  from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-2'
union all
select date_add(stat_date,2) as stat_date
       ,stat_period
       ,network_code
       ,network_name
       ,kpi_name
       ,kpi_type
       ,kpi_fz
       ,kpi_fm
       ,kpi_values
       ,kpi_param
       ,kpi_user
       ,redo_date_cnt
       ,remark
       ,kpi_timely
       ,date_add(dt,2)  as dt
       ,kpi_code
  from jms_dwm.dwm_overall_target_network_sum_mid_dt where dt >=date_sub('{{ execution_date | cst_ds }}',31) and dt<='{{ execution_date | cst_ds }}' and kpi_timely='t-3'
) a
left join jms_dim.dim_network_whole_massage b
  on a.network_code = b.code
left join (select agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]  as principal_name
             from jms_dim.dim_network_whole_massage where network_type =2   ---代理区负责人
            group by agent_code,agent_name,virt_code,virt_name,split(principal,"/")[1]
          ) c
  on b.agent_code = c.agent_code
left join (select code,name,principal from jms_dim.dim_network_whole_massage where network_type = '3'
            group by code,name,principal
          ) d
  on b.fran_code =d.code
distribute by dt;